package edu.jhu.secondhome.database.accessors;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jhu.secondhome.database.ConnectionPool;
import edu.jhu.secondhome.database.DataUtil;
import edu.jhu.secondhome.database.beans.User;

public class UserAccessor extends Accessor {
	
	/**
	 * Gets all users from the database
	 * @return List<User>
	 * @throws SQLException
	 */
	public List<User> getUsers() throws SQLException {
		List<User> users = new ArrayList<User>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT * FROM Users");
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				users.add(extractUser(rs));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return users;
	}
	
	/**
	 * Gets all user email addresses from the database
	 * @return List<String>
	 * @throws SQLException
	 */
	public List<String> getUserEmails() throws SQLException {
		List<String> users = new ArrayList<String>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			ps = conn.prepareStatement("SELECT email FROM Users");
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				users.add(rs.getString("email"));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return users;
	}
	
	/**
	 * Gets all user email addresses from the database whose admin status matches parameter value
	 * @param admin - boolean representing admin privileges
	 * @return List<String>
	 * @throws SQLException
	 */
	public List<String> getUserEmailsAdminStatus(final boolean admin) throws SQLException {
		List<String> users = new ArrayList<String>();
		
		final ConnectionPool pool = ConnectionPool.getInstance();
		final Connection conn = pool.getConnection();
		PreparedStatement ps = null;
		
		try {
			final int adminNum = admin ? 1 : 0;
			ps = conn.prepareStatement("SELECT email FROM Users WHERE admin = \'" + adminNum + "\'");
			ResultSet rs = ps.executeQuery();
			// Move to first result:
			rs.next();
			// Process results
			while (!rs.isAfterLast()) {
				users.add(rs.getString("email"));
				rs.next();
			}
		} finally {
			DataUtil.closeStatement(ps);
			pool.freeConnection(conn);
		}
		return users;
	}
	
	/**
	 * Gets the user registered to the provided e-mail.  Returns null if no user
	 * is currently registered to the provided e-mail.
	 * @return User or null
	 * @throws SQLException
	 */
	public User getUserByEmail( String email ) throws SQLException {
	  final ConnectionPool pool = ConnectionPool.getInstance();
	  final Connection conn = pool.getConnection();
	  PreparedStatement ps = null;
	  
	  try {
	    ps = conn.prepareStatement("SELECT * FROM Users WHERE email = ?");
	    ps.setString( 1, email );
	    ResultSet rs = ps.executeQuery();
	    
	    // Move to first result:
	    boolean available = rs.next();
	    // Process results
	    if ( available )
	      return extractUser( rs );
	  } finally {
	    DataUtil.closeStatement(ps);
	    pool.freeConnection(conn);
	  }
	  return null;
	}
	
	/**
	 * Inserts a new user into the database
	 * @param user - bean filled with the information to add
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int insertUser(User user) throws SQLException {
		String sql = "INSERT INTO Users (email, password, " +
			"firstName, lastName, phoneNumber, currentAddress, " +
			"billingAddress, creditCardNum, creditCardExpMonth, " +
			"creditCardExpYear, creditCardSecCode, admin" + 
			") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		Object[] params = {
				user.getEmail(),
				user.getPassword(),
				user.getFirstName(),
				user.getLastName(),
				user.getPhoneNumber(),
				user.getCurrentAddress(),
				user.getBillingAddress(),
				user.getCreditCardNum(),
				user.getCreditCardExpMonth(),
				user.getCreditCardExpYear(),
				user.getCreditCardSecCode(),
				user.isAdmin()
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates the user information.  NOTE that this does NOT include the 
	 * password field!
	 * @param user - bean filled with information to update the user
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateUser(User user) throws SQLException {
		String sql = "UPDATE Users SET " +
			"email = ?, firstName = ?, lastName = ?, phoneNumber = ?, " +
			"currentAddress = ?, billingAddress = ?, creditCardNum = ?, " +
			"creditCardExpMonth = ?, creditCardExpYear = ?, " +
			"creditCardSecCode = ?, " + "admin = ? " +
			"WHERE email = ?";
		
		Object[] params = {
		    user.getEmail(),
				user.getFirstName(),
				user.getLastName(),
				user.getPhoneNumber(),
				user.getCurrentAddress(),
				user.getBillingAddress(),
				user.getCreditCardNum(),
				user.getCreditCardExpMonth(),
				user.getCreditCardExpYear(),
				user.getCreditCardSecCode(),
				user.isAdmin(),
				user.getEmail()
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates the user's billing information
	 * @param user - bean filled with information to update the user's billing info
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateUserBillingInfo(final String email, 
			final String billingAddress, final String cardNum, 
			final String cardExpMonth, final String cardExpYear, 
			final String cardCode) throws SQLException {
		String sql = "UPDATE Users SET " +
			"billingAddress = ?, creditCardNum = ?, " +
			"creditCardExpMonth = ?, creditCardExpYear = ?, " +
			"creditCardSecCode = ? " +
			"WHERE email = ?";
		
		Object[] params = {
				billingAddress,
				cardNum,
				cardExpMonth,
				cardExpYear,
				cardCode,
				email
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates the user password to the value in the bean for the email in the bean
	 * @param user  - User bean with the email and NEW password to save
	 * 			(this is probably not the best way to go about that but 
	 * 			for now it's here...)
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateUserPassword(User user) throws SQLException {
		String sql = "UPDATE Users SET " +
			"password = ? " + 
			"WHERE email = ?";
		
		String[] params = {
				user.getPassword(),
				user.getEmail()
		};
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Updates the user password to the value for the user whose email is passed in
	 * @param email - email of user whose password to update
	 * @param pass - new password
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int updateUserPassword(final String email, final String pass) throws SQLException {
		String sql = "UPDATE Users SET " +
			"password = ? " + 
			"WHERE email = ?";
		
		String[] params = { pass, email };
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Deletes a user from the database
	 * @param user - bean with the email of the user to delete
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteUser(User user) throws SQLException {
		String sql = "DELETE FROM Users " + 
			"WHERE email = ?";
		
		String[] params = { user.getEmail() };
		
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Deletes a user from the database
	 * @param email - email addr of the user to delete
	 * @return int (from PreparedStatement: the row count for SQL Data 
	 * 			Manipulation Language (DML) statements or 0 for statements 
	 * 			that return nothing)
	 * @throws SQLException
	 */
	public int deleteUser(final String email) throws SQLException {
		String sql = "DELETE FROM Users " + 
			"WHERE email = ?";
		
		String[] params = { email };
		
		return executeUpdateStatement(sql, params);
	}
	
	
	public int editAdminPriviledges(final String email, final boolean admin) throws SQLException {
		String sql = "UPDATE Users SET admin = ? WHERE email = ?";
		Object[] params = { admin, email };
		return executeUpdateStatement(sql, params);
	}
	
	/**
	 * Helper method to extract the user information from the ResultSet 
	 * row and put it into the bean
	 * @param rs - ResultSet pointing to the row of data to pull from
	 * @return populated User object
	 * @throws SQLException
	 */
	private User extractUser(ResultSet rs) throws SQLException {
		return new User(rs.getString("email"), rs.getString("password"), rs.getString("firstName"), 
				rs.getString("lastName"), rs.getString("phoneNumber"), 
				rs.getString("currentAddress"), rs.getString("billingAddress"), 
				rs.getString("creditCardNum"), rs.getString("creditCardExpMonth"), 
				rs.getString("creditCardExpYear"), rs.getString("creditCardSecCode"), rs.getBoolean("admin"));
	}
}
